package com.msite.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.*;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by tpeng on 2016/3/30.
 */
public class ExcelUtil {

    public static void main(String[] args) {
        /*try {
            String path1 = "C:\\Users\\tpeng\\Desktop\\py\\副本2015年地勤收入-机关-补充节油奖20160330.xls";
            String path2 = "C:\\Users\\tpeng\\Desktop\\py\\2016-03发放版.xls";
            newExcelAsFile(path1, path2);
        } catch (IOException e) {
            e.printStackTrace();
        }*/

        try {
            String path1 = "C:\\Users\\tpeng\\Desktop\\a\\a.xls";
            aaanewExcelAsFile(path1, path1);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    /**
     * 得到Excel，并解析内容
     * @param file
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static Map<String, BigDecimal> getExcelAsFile(String file) throws FileNotFoundException, IOException{
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        int trLength = sheet.getLastRowNum();
        Map<String, BigDecimal> rmap = new HashMap<String, BigDecimal>();
        for(int i=0;i<trLength;i++){
            HSSFRow row = sheet.getRow(i);
            String idcard = getValue(row.getCell(2)).toLowerCase();
            String cz = getValue(row.getCell(103));
            if(i == 0){
                //System.out.println(idcard + "=>" + cz);
                continue;
            }
            //System.out.println(idcard+"=>"+cz);
            BigDecimal czNum = cz != null && !"".equals(cz) ? new BigDecimal(cz) : BigDecimal.ZERO;
            if(idcard == null || idcard.equals("") || idcard.equals("null")){
                String gh = getValue(row.getCell(0));
                String name = getValue(row.getCell(1));
                System.out.println(gh+"----"+name+"----"+"身份证号为空");
            }
            if(cz == null || "".equals(cz) || cz.equals("null")){
                String gh = getValue(row.getCell(0));
                String name = getValue(row.getCell(1));
                System.out.println(gh+"----"+name+"----"+"年金为空");
            }
            rmap.put(idcard, czNum);
        }
        return rmap;
    }

    /**
     * 得到Excel，并解析内容
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void newExcelAsFile(String filePath, String filePath2) throws FileNotFoundException, IOException {
        Map<String, BigDecimal> rmap = getExcelAsFile(filePath2);

        System.out.println("-----------------------------");
        File file = new File(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        int trLength = sheet.getLastRowNum();
        for(int i=0;i<trLength;i++){
            HSSFRow row = sheet.getRow(i);
            HSSFCell czCell = row.createCell(105);
            if(i == 0){
                czCell.setCellValue("年金");
                continue;
            }
            String idcard = getValue(row.getCell(1)).toLowerCase();
            BigDecimal cz = rmap.get(idcard);
            if(cz != null){
                czCell.setCellValue(cz.toString());
            }else{
                String gh = getValue(row.getCell(0));
                String idc = getValue(row.getCell(1));
                String name = getValue(row.getCell(2));
                System.out.println(gh+"----"+idc+"----"+name+"----"+"没找到对应");
            }
        }

        FileOutputStream fout = new FileOutputStream(file.getParent()+"\\new.xls");
        wb.write(fout);
        fout.close();
    }


    public static Map<String, Object[]> aaaExcelAsFile(String file) throws FileNotFoundException, IOException{
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(2);
        int trLength = sheet.getLastRowNum();
        Map<String, Object[]> rmap = new HashMap<String, Object[]>();
        for(int i=0;i<trLength;i++){
            HSSFRow row = sheet.getRow(i);
            String zkzh = getValue(row.getCell(9)).toLowerCase();
            String kc = getValue(row.getCell(10));
            String zh = getValue(row.getCell(11));
            if(i == 0){
                //System.out.println(idcard + "=>" + cz);
                continue;
            }
            //System.out.println(idcard+"=>"+cz);
            if(zkzh == null || zkzh.equals("") || zkzh.equals("null")){
                System.out.println(i+"准考证号为空");
            }
            rmap.put(zkzh, new Object[]{ kc, zh });
        }
        return rmap;
    }

    public static void aaanewExcelAsFile(String filePath, String filePath2) throws FileNotFoundException, IOException {
        Map<String, Object[]> rmap = aaaExcelAsFile(filePath2);

        System.out.println("-----------------------------");
        File file = new File(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(2);
        int trLength = sheet.getLastRowNum();
        for(int i=0;i<trLength;i++){
            HSSFRow row = sheet.getRow(i);
            if(row == null){
                System.out.println("--null-"+i);
                continue;
            }
            HSSFCell cz4Cell = row.createCell(4);
            HSSFCell cz5Cell = row.createCell(5);
            HSSFCell cz6Cell = row.createCell(6);
            if(i == 0){
                cz4Cell.setCellValue("准考号");
                cz5Cell.setCellValue("考场");
                cz6Cell.setCellValue("座号");
                continue;
            }
            if(row.getCell(0) == null){
                continue;
            }
            String zkzh = getValue(row.getCell(0)).toLowerCase();
            Object[] cz = rmap.get(zkzh);
            if(cz != null){
                cz4Cell.setCellValue(zkzh);
                cz5Cell.setCellValue(String.valueOf(cz[0]));
                cz6Cell.setCellValue(String.valueOf(cz[1]));
            }else{
                System.out.println(zkzh+"----"+"没找到对应");
            }
        }

        FileOutputStream fout = new FileOutputStream(file.getParent()+"\\new_1.xls");
        wb.write(fout);
        fout.close();
    }

    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }

    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

}
